﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class DdhbDAL:IDdhbDAL
    {
        public int AddDdhb(DdhbInfo ddhb)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, ddhb.Num),
                
                DALHelper.MakeInParam(":dday", OracleType.VarChar, ddhb.Dday),
                DALHelper.MakeInParam(":banbie", OracleType.VarChar, ddhb.Banbie),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, ddhb.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, ddhb.Tianbao),
                DALHelper.MakeInParam(":wb", OracleType.VarChar, ddhb.Wb),
                DALHelper.MakeInParam(":sc", OracleType.VarChar, ddhb.Sc),
                DALHelper.MakeInParam(":sb", OracleType.VarChar, ddhb.Sb),
                DALHelper.MakeInParam(":zl", OracleType.VarChar, ddhb.Zl),
                DALHelper.MakeInParam(":aq", OracleType.VarChar, ddhb.Aq)
            };
            return (DALHelper.RunCommand(@"insert into dd.ddhb values (:num, to_date(:dday,'yyyy-mm-dd hh24:mi:ss'), :banbie, :banzu, :tianbao, :wb, :sc, :sb, :zl, :aq)", prams));
        }
        public int UpdateDdhb(string num, string banbie, string banzu, string wb, string sc, string sb, string zl, string aq)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                
                
                DALHelper.MakeInParam(":banbie", OracleType.VarChar, banbie),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, banzu),
                
                DALHelper.MakeInParam(":wb", OracleType.VarChar, wb),
                DALHelper.MakeInParam(":sc", OracleType.VarChar, sc),
                DALHelper.MakeInParam(":sb", OracleType.VarChar, sb),
                DALHelper.MakeInParam(":zl", OracleType.VarChar, zl),
                DALHelper.MakeInParam(":aq", OracleType.VarChar, aq)
            };
            return (DALHelper.RunCommand(@"update dd.ddhb set banbie = :banbie, banzu = :banzu, wb = :wb, sc = :sc, sb = :sb, zl = :zl, aq = :aq where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.ddhb where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.ddhb where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string sday)
        {
            string bday = sday + "00:00:00";
            string eday = sday + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bday", OracleType.VarChar, bday),
                DALHelper.MakeInParam(":eday", OracleType.VarChar, eday)
            };

            return (DALHelper.RunProcReturn(@"select * from dd.ddhb where dday >= to_date(:bday,'yyyy-mm-dd hh24:mi:ss') and dday <= to_date(:eday,'yyyy-mm-dd hh24:mi:ss')", prams, "Sbyday"));
        
        }
        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "ddhb" + time.ToString();


            return num;
        }
        public DataSet ddinfo()
        {
            return (DALHelper.RunProcReturn(@"select * from (select wb, sc, sb, zl, aq from dd.ddhb order by dday desc) where rownum < 2", "ddxx"));
        }
    }
}
